﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DBHelper
    {
        private static string DBConnectString = "server=.;database=library_management;uid=sa;pwd=123456;";
        private static SqlConnection sqlCon;
        private static SqlCommand sqlCmd;

        public void OpenConnection()
        {
            if (sqlCon == null)
            {
                sqlCon = new SqlConnection(DBConnectString);
                sqlCon.Open();
            }
            else
            {
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }
            }
        }

        public void CloseConnection()
        {
            if (sqlCon != null)
            {
                if (sqlCon.State == ConnectionState.Open)
                {
                    sqlCon.Close();
                }
            }
        }

        public DataSet SelectData(string sql)
        {
            OpenConnection();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlCon))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                CloseConnection();
                return ds;
            }
        }

        public int ExecuteSql(string sql)
        {
            OpenConnection();
            using (sqlCmd = new SqlCommand(sql, sqlCon))
            {
                int index = sqlCmd.ExecuteNonQuery();
                CloseConnection();
                return index;
            }
        }
    }
}

